import pandas as pd

data = pd.read_csv('51job_bigdata.csv')

#print(data['公司'].duplicated())
# 数据去重
res = data.drop_duplicates(["公司","职位"])

# 清洗数据
df= res[res['职位'].str.contains(r'.*?数据.*?|.*?分析.*?|.*?开发.*?|.*?架构.*?|.*?ETL.*?|.*?技术.*?|.*?工程师.*?')]

# 去除缺失数据
df = df[df['工资'].notnull()]

# 薪资待遇数据规范化

#利用正则表达式提取月薪，把待遇规范成千/月的形式

# 删选出 K 结尾的
salary_K = df[df['工资'].str.contains('.*?K.*?|.*?千.*?')]

# 将 千结尾的转换为  K

# salary_K[['最低工资','最高工资']] = salary_K['工资'].str.replace({r'千/月':''}).str.split('-')
salary_K[['最低工资','最高工资']] = salary_K['工资'].replace(regex={r'(\d+\.?\d*)\-?(\d+\.?\d*)千/月':r'\1K-\2K'}).str.split('-',expand=True)
salary_K.drop('工资', axis=1, inplace=True)

#
# 筛选出万/月结尾的数据
salary_W = df[df['工资'].str.contains('.*?万/月.*?')]
salary_W[['最低工资','最高工资']] = salary_W['工资'].replace(regex={r'(\d+\.?\d*)\-?(\d+\.?\d*)万/月':r'\1-\2'}).str.split('-',expand=True)
# 转换
salary_W['最低工资'] = salary_W['最低工资'].astype("float")
salary_W['最高工资'] = salary_W['最高工资'].astype("float")
salary_W['最低工资'] = (salary_W['最低工资']*10).astype("str") + 'K'
salary_W['最高工资'] = (salary_W['最高工资']*10).astype("str") + 'K'
salary_W.drop('工资', axis=1, inplace=True)




#
# # 将 万结尾的转换为 K
#
# # 筛选出万/年结尾的数据
salary_W_Y = df[df['工资'].str.contains('.*?万/年.*?')]
salary_W_Y[['最低工资','最高工资']] = salary_W_Y['工资'].replace(regex={r'(\d+\.?\d*)\-?(\d+\.?\d*)万/年':r'\1-\2'}).str.split('-',expand=True)

# 将 / 年 结尾的数据转换为  K
salary_W_Y['最低工资'] = salary_W_Y['最低工资'].astype("float")
salary_W_Y['最高工资'] = salary_W_Y['最高工资'].astype("float")
salary_W_Y['最低工资'] = (salary_W_Y['最低工资']*10/12).round(decimals=2).astype("str") + 'K'
salary_W_Y['最高工资'] = (salary_W_Y['最高工资']*10/12).round(decimals=2).astype("str") + 'K'
salary_W_Y.drop('工资', axis=1, inplace=True)


# 合并
all = salary_K.append(salary_W).append(salary_W_Y)
all.to_csv("./final_jobinfo.csv")

# salary_K.to_csv("./k.csv")
# print(salary_K)
# salary_K.to_csv("./w.csv")
# print(salary_W)
# salary_K.to_csv("./w_y.csv")
# print(salary_W_Y)




# 最后保存为 csv



